package com.cindata

import com.cindata.dimension.BuildAreaDimension
import com.cindata.dimension.CustomDimension
import com.cindata.dimension.RiskDimension
import com.cindata.dimension.YearLimitDimension
import grails.converters.JSON
import org.hibernate.cfg.ImprovedNamingStrategy
import org.springframework.security.access.annotation.Secured

@Secured(['isAuthenticated()'])
class MapController
{
    def dataSource
    def springSecurityService
    def apiService

    def index()
    {
    }

    def heat()
    {
        respond(view: "heat")
    }

    def map()
    {
        respond(view: "map")
    }

    def aroundAccount()
    {
        respond(view: "aroundAccount")
    }

    /**
     * 行政区下不良资产数据*/
    def nonPerformingAssetData()
    {
        def mapDataStatus = ""
        def result = new HashMap()
        def mapDataList = new ArrayList()
        def conditionQueryParam = new HashMap()
        try
        {
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = mapDataList
                render([result: result] as JSON)
                return
            }
            def nonPerformingAssetSql = " select new map (e.name as name ,e.x as  lng,e.y as lat, e.id as id ,e.externalId as externalId ,e.communityName as communityName ," + "e.sourceAddress as sourceAddress ,e.sourceBuildArea as sourceBuildArea ,e.loansBalance as loansBalance , " + "e.loansDuration as loansDuration ,e.loansAmount as loansAmount,e.loansEvaluateTotalPrice as loansEvaluateTotalPrice ," + "to_char(e.startDate,'yyyy-MM-dd') as startDate ,e.cltv*100 as cltv ," + "e.loadArea as loadArea , e.buildingType.name as buildingType , e.purchaseTotalPrice as purchaseTotalPrice ," + "e.auctionPrice as auctionPrice ,e.purchaseUnitPrice as purchaseUnitPrice ) " + "from Estate e where e.isNonPerformingAsset = true "
            nonPerformingAssetSql = mapConditionSql(nonPerformingAssetSql, params, conditionQueryParam, user, 1)
            mapDataList = Estate.executeQuery(nonPerformingAssetSql, conditionQueryParam)
            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /**
     * 行政区下小区不良资产数据*/
    def communityNonPerformingAssetData()
    {
        def mapDataStatus = ""
        def result = new HashMap()
        def mapDataList = new ArrayList()
        def conditionQueryParam = new HashMap()
        try
        {
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = mapDataList
                render([result: result] as JSON)
                return
            }
            def communitySql = " select new map(e.communityId as communityId, max(e.communityName) as communityName ,sum(e.loansAmount) as price ,count(e.id) as count,avg(e.x )as lng, avg(e.y) as lat ) from Estate e where e.isNonPerformingAsset = true  "
            communitySql = mapConditionSql(communitySql, params, conditionQueryParam, user, 1)
            communitySql += " group by e.communityId "
            mapDataList = Estate.executeQuery(communitySql, conditionQueryParam)
            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /**
     * 小区下楼栋不良资产数据*/
    def buildingNonPerformingAssetData()
    {
        def mapDataStatus = ""
        def result = new HashMap()
        def mapDataList = new ArrayList()
        def conditionQueryParam = new HashMap()
        def community = new ArrayList()
        def city = params['city']
        def communityId = params['communityId']
        try
        {
            if (!communityId)
            {
                return
            }
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = mapDataList
                render([result: result] as JSON)
                return
            }
            def buildSql = " select new map(e.x as  lng,e.y as lat ) from Estate e where e.isNonPerformingAsset = true "
            def nonPerformingAssetSql = " select new map (e.name as name ,e.x as  lng,e.y as lat, e.id as id ,e.externalId as externalId ,e.communityName as communityName ," + "e.sourceAddress as sourceAddress ,e.sourceBuildArea as sourceBuildArea ,e.loansBalance as loansBalance , " + "e.loansDuration as loansDuration ,e.loansAmount as loansAmount,e.loansEvaluateTotalPrice as loansEvaluateTotalPrice ," + "to_char(e.startDate,'yyyy-MM-dd') as startDate ,e.cltv*100 as cltv ," + "e.loadArea as loadArea , e.buildingType.name as buildingType , e.purchaseTotalPrice as purchaseTotalPrice ," + "e.auctionPrice as auctionPrice ,e.purchaseUnitPrice as purchaseUnitPrice ) " + "from Estate e where e.isNonPerformingAsset = true "
            nonPerformingAssetSql = mapConditionSql(nonPerformingAssetSql, params, conditionQueryParam, user, 1)
            buildSql = mapConditionSql(buildSql, params, conditionQueryParam, user, 1)
            buildSql += " group by e.x,e.y "
            mapDataList = Estate.executeQuery(buildSql, conditionQueryParam)
            mapDataList?.each {
                it.put("estateList", new ArrayList<>())
            }
            for (
                int j = 0;
                    j < mapDataList.size();
                    j++)
            {
                def str = nonPerformingAssetSql
                str += " and e.x = :lng and e.y = :lat "
                conditionQueryParam.lng = mapDataList[j].lng
                conditionQueryParam.lat = mapDataList[j].lat
                def elist = Estate.executeQuery(str, conditionQueryParam)
                mapDataList[j].put("estateList", elist)
            }
            //小区详情
            community = apiService.community(city, Long.parseLong(communityId))
            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result["community"] = community["data"]
            result["mapDataList"] = mapDataList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["community"] = community["data"]
            result["mapDataList"] = mapDataList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /**
     * 不良资产周边案例*/
    def caseData()
    {
        def mapDataStatus = ""
        def result = new HashMap()
        def mapDataList = new ArrayList()
        def conditionQueryParam = new HashMap()
        try
        {

            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /**
     * 周边网点地图数据*/
    def aroundAccountMapData()
    {
        def mapDataStatus = ""
        def result = new HashMap()
        def mapDataList = new ArrayList()
        def conditionQueryParam = new HashMap()
        def city = params["city"]
        def province = params["province"]
        //城市
        def level = params["level"]
        //片区
        try
        {
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = mapDataList
                render([result: result] as JSON)
                return
            }
            if (city == "-1" && level == "1")
            {
                //全国下的省网点
                def aroundAccountSql = " select new map( a.name as name ,a.longitude as lng ,a.latitude as lat ,a.zoneNumber as zoneNumber ,count(distinct e.account.id) as count ) from Estate e join e.province a where e.isEstate = true "
                aroundAccountSql = aroundAccountConditionSql(aroundAccountSql, params, conditionQueryParam, user, 2)
                aroundAccountSql += " group by a.name ,a.zoneNumber ,a.longitude,a.latitude "
                mapDataList = Estate.executeQuery(aroundAccountSql, conditionQueryParam)
            }
            else if (province && province != "-1" && level == "2")
            {
                //省下的市网点
                def aroundAccountSql = " select new map( a.name as name ,a.longitude as lng ,a.latitude as lat ,a.zoneNumber as zoneNumber ,count(distinct e.account.id) as count ) from Estate e join e.city a where e.isEstate = true "
                aroundAccountSql = aroundAccountConditionSql(aroundAccountSql, params, conditionQueryParam, user, 2)
                aroundAccountSql += " group by a.name ,a.zoneNumber ,a.longitude,a.latitude "
                mapDataList = Estate.executeQuery(aroundAccountSql, conditionQueryParam)
            }
            else if (city && city != '-1' && level == "3")
            {
                //市下的行政区网点
                def aroundAccountSql = " select new map ( a.name as name , a.zoneNumber as zoneNumber,a.longitude as lng ,a.latitude as lat ,count(distinct e.account.id) as count) from Estate e inner join e.district a where e.isEstate = true "
                aroundAccountSql = aroundAccountConditionSql(aroundAccountSql, params, conditionQueryParam, user, 2)
                aroundAccountSql += " group by a.name ,a.zoneNumber,a.longitude,a.latitude "
                mapDataList = Estate.executeQuery(aroundAccountSql, conditionQueryParam)
            }
            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /*
    * 获取所有省份网点数据
    * */

    def getProvinceForAccount()
    {
        def db = new groovy.sql.Sql(dataSource)
        def sql = "SELECT a.zone_number,count(1) FROM account t left join area a on t.province_id = a.area_id GROUP BY t.province_id , a.zone_number"
        def result = db.rows(sql).asList()
        render([result: result] as JSON)
    }

    /*
    * 根据省份id获取城市数据
    * */

    def getCityForAccount()
    {
        def db = new groovy.sql.Sql(dataSource)
        def id = params["province"];
        def sql =
            "SELECT\n" + "	a2.zone_number as zone_number,\n" + "	T . C as count\n" + "FROM\n" + "	(\n" + "		SELECT\n" + "			A .zone_number,\n" + "			o.city_id,\n" + "			COUNT (1) AS C\n" + "		FROM\n" + "			account o\n" + "		LEFT JOIN area A ON A .area_id = o.province_id\n" + "		WHERE\n" + "	        zone_number = '" + id + "'\n" + "		GROUP BY\n" + "			o.city_id,\n" + "			A .zone_number\n" + "	) T\n" + "LEFT JOIN area a2 ON a2.area_id = T .city_id"
        def result = db.rows(sql).asList()
        render([result: result] as JSON)
    }

    /*
    * 根据城市,获取区数据
    * */

    def getDistrictForAccount()
    {
        def db = new groovy.sql.Sql(dataSource);
        def id = params['city']
        def sql =
            "SELECT\n" + "	A .zone_number,\n" + "	T . COUNT\n" + "FROM\n" + "	(\n" + "		SELECT\n" + "			A .zone_number AS zone_number,\n" + "			o.district_id AS ID,\n" + "			COUNT (1)\n" + "		FROM\n" + "			account o\n" + "		LEFT JOIN area A ON o.city_id = A .area_id\n" + "		WHERE\n" + "	        A .zone_number = '" + id + "'\n" + "		GROUP BY\n" + "			A .zone_number,\n" + "			o.district_id\n" + "	) T\n" + "LEFT JOIN area A ON T . ID = A .area_id";
        def result = db.rows(sql).asList()
        render([result: result] as JSON)
    }

    /*
    * 获取银行数据
    * */

    def getBankForAccount()
    {
        def db = new groovy.sql.Sql(dataSource)
        def id = params["district"]
        def sql =
            "SELECT\n" + "	T .*, dd.code\n" + "FROM\n" + "	(\n" + "		SELECT\n" + "			o.x,\n" + "			o.y,\n" + "			o. ID,\n" + "			o.\"name\",\n" + "			o.level_id\n" + "		FROM\n" + "			account o\n" + "		LEFT JOIN area A ON o.district_id = A .area_id\n" + "		WHERE\n" + "			A .zone_number = '" + id + "'\n" + "	) T\n" + "LEFT JOIN data_dict dd ON T .level_id = dd. ID"
        def bankList = db.rows(sql).asList();
        print bankList
        render([bankList: bankList] as JSON)
    }

    /*
    * 获取银行管辖范围以及小区
    * */

    def getBankForSourceId()
    {
        def resultList = new ArrayList()
        def db = new groovy.sql.Sql(dataSource)
        def id = params["sourceId"]
        def city = params['city']
        def sql =
            "SELECT\n" + "	C . NAME,\n" + "	C .coordinates,\n" + "	C .id_list\n" + "FROM\n" + "	(\n" + "		SELECT\n" + "			*\n" + "		FROM\n" + "			account o\n" + "		LEFT JOIN area A ON o.district_id = A .area_id\n" + "	) T\n" + "LEFT JOIN custom_region C ON T . ID = C .source_id\n" + "WHERE\n" + "	C .source_id = ' " + id + "'"

        def boundList = db.rows(sql).asList();
        if (boundList.size() > 0)
        {
            boundList.each {
                def result = new HashMap();
                result['name'] = it.NAME;
                result['coordinates'] = it.coordinates;
                if (it.id_list == "")
                {
                    result['total'] = 0;
                }
                else
                {
                    result['total'] = it.id_list.split(';').size();
                }
                def re = new ArrayList();
                def house = 0;
                def list = it.id_list.split(';');
                if (list.length > 1)
                {
                    list.each {
                        if (it.split(',').length > 1)
                        {
                            def ls = it.split(',')[1];
                            if (ls != " ")
                            {
                                re.add(ls);
                            }
                        }
                    }
                    re = removeDuplicate(re);
                    re.each {
                        if (it != null)
                        {
                            house += apiService.community(city, Long.parseLong(it)).data.totalHouseHoldCount;
                        }
                    }
                    result['house'] = house;
                }
                else
                {
                    result['house'] = 0;
                }
                resultList.add(result)
            }
        }
        render([resultList: resultList] as JSON)
    }

    /*
    * list去重
    * */

    private static def removeDuplicate(List<String> list)
    {
        LinkedHashSet<String> set = new LinkedHashSet<String>(list.size());
        set.addAll(list);
        list.clear();
        list.addAll(set);
        return list;
    }

    /**
     * 片区下网点搜索*/
    def accountSearchData()
    {
        def mapDataStatus = ""
        def result = new HashMap()
        def mapDataList = new ArrayList()
        def conditionQueryParam = new HashMap()
        try
        {
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = mapDataList
                render([result: result] as JSON)
                return
            }
            def accountSql = " select new map( a.x as lng ,a.y as lat ,a.name as accountName ,a.id as accountId ,a.address as address , a.logo as logo ,a.city.zoneNumber as zoneNumber ) from Estate e join e.account a where 1=1 "
            accountSql = aroundAccountConditionSql(accountSql, params, conditionQueryParam, user, 1)
            accountSql += " group by a.name ,a.id  ,a.x,a.y ,a.address ,a.logo ,a.city.zoneNumber "
            mapDataList = Estate.executeQuery(accountSql, conditionQueryParam)
            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /**
     * 行政区下片区网点数据*/
    def accountPrecinctData()
    {
        def mapDataStatus = ""
        def result = new HashMap()
        def mapDataList = new ArrayList()
        def conditionQueryParam = new HashMap()
        try
        {
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = mapDataList
                render([result: result] as JSON)
                return
            }
            def precinctSql = " select new map ( a.name as name , a.id as precinctId,a.longitude as lng ,a.latitude as lat ,a.coordinates as coordinates ,count(distinct e.account.id) as count) from Estate e inner join e.precinct a where a.precinctType = '周边网点' and a. isDeleted = 'false' "
            precinctSql = aroundAccountConditionSql(precinctSql, params, conditionQueryParam, user, 2)
            precinctSql += " group by a.name ,a.id,a.longitude,a.latitude ,a.coordinates "
            mapDataList = Estate.executeQuery(precinctSql, conditionQueryParam)
            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /**
     * 网点周边小区及押品数据*/
    def accountCommunityData()
    {
        def mapDataStatus = ""
        def result = new HashMap()
        def communityList = new ArrayList()
        def boundaryList = new ArrayList()
        def conditionQueryParam = new HashMap()
        def estateQueryParam = new HashMap()
        def isEstate = ""
        def city = params['city']
        try
        {
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = communityList
                render([result: result] as JSON)
                return
            }
            def communitySql = " select new map ( e.communityId as communityId , e.communityName as communityName ) from Estate e where 1=1 "
            def estateSql = "select new map(e.id as id ,e.externalId as externalId ,e.sourceAddress as sourceAddress ,e.x as lng, e.y as lat,e.isEstate as isEstate ) " + "from Estate e where e.isEstate = true "
            def boundarySql = " select new map ( c.coordinates as coordinates ) from  com.cindata.CustomRegion c where c.sourceClass = 'com.cindata.Account' and c.precinctType = 'AroundAccount' and c.sourceId = ${params['accountId']} "
            communitySql = aroundAccountConditionSql(communitySql, params, conditionQueryParam, user, 1)
            estateSql = aroundAccountConditionSql(estateSql, params, estateQueryParam, user, 1)
            communitySql += " group by e.communityId ,e.communityName "
            //查询网点小区信息
            communityList = Estate.executeQuery(communitySql, conditionQueryParam)
            boundaryList = CustomRegion.executeQuery(boundarySql).asList()
            communityList.each {
                it.put("buildList", new ArrayList<>())
            }
            for (
                int i = 0;
                    i < communityList.size();
                    i++)
            {
                //接口查询小区所有楼栋
                def mapDataList = apiService.building(city, communityList[i].communityId, "")["data"]
                for (
                    int k = 0;
                        k < mapDataList.size();
                        k++)
                {
                    def x = mapDataList[k].x as String
                    def y = mapDataList[k].y as String
                    def bdzb = Trans.MctToBd(x, y)
                    bdzb = bdzb.split(',')
                    x = bdzb[0]
                    y = bdzb[1]
                    mapDataList[k].lng = Double.parseDouble(x)
                    mapDataList[k].lat = Double.parseDouble(y)
                }
                def blist = (List) communityList[i].get("buildList")
                blist.add(mapDataList)
                communityList[i].put("buildList", blist)
                mapDataList.each {
                    it.put("isEstate", new String())
                }
                //查询坐标相同押品信息
                for (
                    int j = 0;
                        j < mapDataList.size();
                        j++)
                {
                    def str = estateSql
                    def buildingId = mapDataList[j].buildingId
                    str += " and e.buildingId = :buildingId "
                    estateQueryParam['buildingId'] = Long.parseLong(buildingId)
                    def elist = Estate.executeQuery(str, estateQueryParam)
                    if (elist.size() > 0)
                    {
                        isEstate = "true"
                    }
                    else
                    {
                        isEstate = "false"
                    }
                    mapDataList[j].put("isEstate", isEstate)
                }
            }
            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = communityList
            result["boundaryList"] = boundaryList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = communityList
            result["boundaryList"] = boundaryList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /**
     * 网点周边小区及押品数据2*/
    def accountCommunityData2()
    {
        def db = new groovy.sql.Sql(dataSource)
        def mapDataStatus = ""
        def result = new HashMap()
        def communityList = new ArrayList()
        def buildList = new ArrayList()
        def boundaryList = new ArrayList()
        def conditionQueryParam = new HashMap()
        def city = params['city']
        try
        {
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = communityList
                render([result: result] as JSON)
                return
            }
            def accountManageAccountSql = " select o.manageAccount.id from AccountManageAccount o where o.account.id = ${userAccount.id} "
            def accountIdList = AccountManageAccount.executeQuery(accountManageAccountSql).toString().replace("[", "(").replace("]", ")")
            def communitySql = " select new map ( e.communityId as communityId , e.communityName as communityName ) from Estate e where 1=1 "
            def buildSql = " select new map ( e.x as  lng,e.y as lat ) from Estate e where e.isEstate = true "
            def estateSql = "select e.x as  lng,e.y as lat, c.define as define ,c.color as color ,e.id as id ,e.external_Id as externalId ,e.community_Name as communityName ," + "e.source_Address as sourceAddress ,e.source_Build_Area as sourceBuildArea ,e.loans_Balance as loansBalance , e.loans_Duration as loansDuration ," + "e.x as lng, e.y as lat,e.loans_Amount as loansAmount,e.loans_Evaluate_Total_Price as loansEvaluateTotalPrice ," + "to_char(e.start_Date,'yyyy-MM-dd') as startDate ,e.cltv*100 as cltv from Estate e " + "join custom_dimension c on c.parent_id=(select d.id from custom_dimension d where d.code='cltv') and e.cltv > c.min_value and e.cltv <= c.max_value " + "join area d on d.area_id=e.province_id join account o on o.id=e.account_id  " + "join area a on a.area_id=e.city_id join area b on b.area_id=e.district_id where  e.is_Estate = true and e.account_id in " + accountIdList + " "
            def boundarySql = " select new map ( c.coordinates as coordinates ) from  com.cindata.CustomRegion c where c.sourceClass = 'com.cindata.Account' and c.precinctType = 'AroundAccount' and c.sourceId = ${params['accountId']} "
            communitySql = aroundAccountConditionSql(communitySql, params, conditionQueryParam, user, 1)
            buildSql = aroundAccountConditionSql(buildSql, params, conditionQueryParam, user, 1)
            estateSql += aroundAccountConditionSQL(params)
            communitySql += " group by e.communityId ,e.communityName "
            //周边网点边界
            boundaryList = CustomRegion.executeQuery(boundarySql, [max: 10, offset: 0]).asList()
            //查询网点小区信息
            communityList = Estate.executeQuery(communitySql, conditionQueryParam, [max: 10, offset: 0])
            communityList?.each { Map<String, Object> map ->
                map.put("buildList", new ArrayList())
                map.put("bound", new String())
                map.put("totalHouseHoldCount", 0)
            }
            for (
                int i = 0;
                    i < communityList.size();
                    i++)
            {
                //接口返回小区总户数
                def community = apiService.community(city, communityList[i]["communityId"])
                communityList[i].put("bound", community?.data?.bound)
                communityList[i].put("totalHouseHoldCount", community?.data?.totalHouseHoldCount)
                def building = buildSql
                building += " and e.communityId = :communityId "
                conditionQueryParam["communityId"] = communityList[i]["communityId"]
                building += " group by e.x ,e.y "
                //楼栋列表
                def buildingList = Estate.executeQuery(building, conditionQueryParam, [max: 10, offset: 0])
                for (
                    int j = 0;
                        j < buildingList.size();
                        j++)
                {
                    def cltvList = CustomDimension.executeQuery(" from  CustomDimension c where c.parent.code = 'cltv' order by c.orderNumber desc  ")
                    def list = new ArrayList()
                    def newList = new ArrayList()
                    cltvList.each {
                        def str = estateSql
                        def x = buildingList[j]["lng"]
                        def y = buildingList[j]["lat"]
                        //                        def bdzb = Trans.BdToMct(x,y)
                        //                        bdzb = bdzb.split(',')
                        //                        x = bdzb[0]
                        //                        y = bdzb[1]
                        def customMinValue = it.minValue
                        def customMaxValue = it.maxValue
                        str += " and e.x = ${x}  and e.y = ${y} and e.cltv ${it.minOperator} ${customMinValue} and e.cltv ${it.maxOperator} ${customMaxValue} order by e.cltv desc "
                        def elist = db.rows(str).asList()
                        if (elist.size() > 0)
                        {
                            list.add(elist)
                        }
                    }
                    for (
                        int k = 0;
                            k < list.size();
                            k++)
                    {
                        def map = new HashMap<String, Object>()
                        map.put("estateList", list[k])
                        map.put("lng", list[k]["lng"][0])
                        map.put("lat", list[k]["lat"][0])
                        map.put("define", list[k]["define"][0])
                        map.put("color", list[k]["color"][0])
                        newList.add(map)
                    }
                    buildList.add(newList)
                }
                communityList[i].put("buildList", buildList)
            }
            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = communityList
            result["boundaryList"] = boundaryList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = communityList
            result["boundaryList"] = boundaryList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /**
     * 网点周边楼栋数据*/
    def accountEstateSearchData()
    {
        def mapDataStatus = ""
        def result = new HashMap()
        def mapDataList = new ArrayList()
        def conditionQueryParam = new HashMap()
        try
        {
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = mapDataList
                render([result: result] as JSON)
                return
            }
            def buildSql = " select new map(e.x as  lng,e.y as lat ,count(case when isEstate = true then 1 else null end) as estate, count(case when isEstate = false then 1 else null end) as noEstate ) from Estate e where 1=1 "
            def estateSql = "select new map(e.id as id ,e.externalId as externalId ,e.communityName as communityName ,e.sourceAddress as sourceAddress ,e.x as lng, e.y as lat,e.cltv*100 as cltv ,e.isEstate as isEstate ) " + "from Estate e where 1=1 "
            buildSql = mapConditionSql(buildSql, params, conditionQueryParam, user, 1)
            estateSql = mapConditionSql(estateSql, params, conditionQueryParam, user, 1)
            buildSql += " group by e.x ,e.y "
            //查询小区楼栋的x/y
            mapDataList = Estate.executeQuery(buildSql, conditionQueryParam)
            mapDataList.each {
                it.put("estateList", new ArrayList<>())
            }
            //查询坐标相同押品信息
            for (
                int j = 0;
                    j < mapDataList.size();
                    j++)
            {
                def str = estateSql
                def x = mapDataList[j].lng
                def y = mapDataList[j].lat
                str += " and e.x = :x and e.y = :y "
                conditionQueryParam['x'] = x
                conditionQueryParam['y'] = y
                def elist = Estate.executeQuery(str, conditionQueryParam)
                def list = (List) mapDataList[j].get("estateList")
                list.add(elist)
                mapDataList[j].put("estateList", list)
            }
            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /**
     * 周边网点地图条件原生SQL*/
    def aroundAccountConditionSQL(params)
    {
        def province = params["province"]
        //省
        def city = params["city"]
        //城市
        def district = params["district"]
        //行政区
        def precinct = params["precinct"]
        //片区
        def accountName = params["accountName"]
        //网点机构名称
        def accountId = params["accountId"]
        def estateSql = " "
        if (city && city != 'null' && city != '-1')
        {
            estateSql += "and a.zone_Number = '${city}' "
        }
        if (district && district != 'null' && district != '-1')
        {
            estateSql += "and b.zone_Number = '${district}' "
        }
        if (precinct && precinct != "-1")
        {
            estateSql += "and p.id = ${precinct} "
        }
        if (province && province != '-1')
        {
            estateSql += " and d.zone_Number = '${province}'"
        }
        if (accountName && accountName != '')
        {
            estateSql += " and o.name like %'${accountName}'% "
        }
        if (accountId)
        {
            estateSql += " and o.id = '${accountId}' "
        }
        return estateSql
    }

    /**
     * 网点周边地图条件*/
    def aroundAccountConditionSql(estateSql, params, conditionQueryParam, user, type)
    {
        def province = params["province"]
        //省
        def city = params["city"]
        //城市
        def district = params["district"]
        //行政区
        def precinct = params["precinct"]
        //片区
        def accountName = params["accountName"]
        //网点机构名称
        def accountId = params["accountId"]
        //网点机构Id
        estateSql = Estate.FilterAccount(estateSql, conditionQueryParam, user)
        if (city && city != '-1')
        {
            if (type == 1)
            {
                estateSql += " and e.city.zoneNumber = :city "
            }
            else if (type == 2)
            {
                estateSql += " and a.parentArea.zoneNumber = :city "
            }
            conditionQueryParam["city"] = city
        }
        if (district && district != '-1')
        {
            estateSql += " and e.district.zoneNumber = :district "
            conditionQueryParam["district"] = district
        }
        if (precinct && precinct != '-1')
        {
            estateSql += " and e.precinct.id = :precinct "
            conditionQueryParam["precinct"] = precinct
        }
        if (province && province != '-1')
        {
            estateSql += " and e.province.zoneNumber = :province "
            conditionQueryParam["province"] = province
        }
        if (accountName && accountName != '')
        {
            estateSql += " and e.account.name like :accountName "
            conditionQueryParam["accountName"] = "%" + accountName + "%"
        }
        if (accountId)
        {
            estateSql += " and e.account.id = :accountId "
            conditionQueryParam["accountId"] = Long.parseLong(accountId)
        }
        return estateSql
    }

    /**
     * 初始化地图数据
     * @return
     */
    def initMapData()
    {
        def mapDataStatus = ""
        def result = new HashMap()
        def mapDataList = new ArrayList()
        def conditionQueryParam = new HashMap()
        try
        {
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = mapDataList
                render([result: result] as JSON)
                return
            }
            def currentMap = params["currentMap"]
            //地图类型
            if (currentMap == "heatMap")
            {
                //热力图
                def heatSql = " select new map (e.communityId as communityId , e.communityName as communityName ,e.city.zoneNumber as zoneNumber, avg(e.x) as lng ,avg(e.y) as lat ,count(e.id) as count ) from Estate e where e.isEstate = true "
                heatSql = mapConditionSql(heatSql, params, conditionQueryParam, user, 1)
                heatSql += " group by e.communityId ,e.communityName ,e.city.zoneNumber "
                mapDataList = Estate.executeQuery(heatSql, conditionQueryParam)
            }
            else if (currentMap == "clusterMap")
            {
                //聚合图
                def clusterSql = " select new map (b.name as name , b.zoneNumber as zoneNumber ,b.longitude as lng ,b.latitude as lat ,count(e.id) as count ) from Estate e join e.district b where e.isEstate = true "
                clusterSql = mapConditionSql(clusterSql, params, conditionQueryParam, user, 2)
                clusterSql += " group by b.name ,b.zoneNumber ,b.longitude ,b.latitude "
                mapDataList = Estate.executeQuery(clusterSql, conditionQueryParam)
            }
            else if (currentMap == "pointMap")
            {
                //散点图
                def pointSql = " select new map(e.x as  lng,e.y as lat,count(e.id) as count) from Estate e where e.isEstate = true "
                pointSql = mapConditionSql(pointSql, params, conditionQueryParam, user, 1)
                pointSql += " group by e.x ,e.y "
                mapDataList = Estate.executeQuery(pointSql, conditionQueryParam)
            }
            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /**
     * 片区地图数据
     * @return
     */
    def mapPrecinctData()
    {
        def mapDataStatus = ""
        def result = new HashMap()
        def mapDataList = new ArrayList()
        def conditionQueryParam = new HashMap()
        try
        {
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = mapDataList
                render([result: result] as JSON)
                return
            }
            def communitySql = " select new map(a.name as name , a.id as precinctId,a.longitude as lng ,a.latitude as lat ,a.coordinates as coordinates ,count(e.id) as count ) from Estate e join e.precinct a where e.isEstate = true "
            communitySql = mapConditionSql(communitySql, params, conditionQueryParam, user, 1)
            communitySql += " group by e.name ,e.id ,a.longitude ,a.latitude ,a.coordinates "
            mapDataList = Estate.executeQuery(communitySql, conditionQueryParam)
            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
            e.printStackTrace()
        }
        render([result: result] as JSON)

    }

    /**
     * 小区地图数据*/
    def mapCommunityData()
    {
        def mapDataStatus = ""
        def result = new HashMap()
        def mapDataList = new ArrayList()
        def conditionQueryParam = new HashMap()
        def city = params["city"]
        def district = params["district"]
        def communityName = params["communityName"]
        def apiCommunityList = new ArrayList()
        try
        {
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = mapDataList
                render([result: result] as JSON)
                return
            }
            //            apiCommunityList = apiService.communityList(city,district,communityName,2000)["data"]
            def communitySql = " select new map(e.communityId as communityId, e.city.zoneNumber as zoneNumber, max(e.communityName) as communityName ,sum(e.loansAmount) as price ,count(e.id) as count,avg(e.x )as lng, avg(e.y) as lat ) from Estate e where e.isEstate = true "
            communitySql = mapConditionSql(communitySql, params, conditionQueryParam, user, 1)
            communitySql += " group by e.communityId ,e.city.zoneNumber "
            mapDataList = Estate.executeQuery(communitySql, conditionQueryParam)
            //            mapDataList?.each {
            //                it.put("address",  new String())
            //            }
            //            mapDataList?.each {
            //                Map map ->
            //                apiCommunityList?.each {
            //                    if(Long.parseLong(it["communityId"])==map["communityId"]){
            //                        map.put("address",it["address"])
            //                        return
            //                    }
            //                }
            //            }
            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
            e.printStackTrace()
        }
        render([result: result] as JSON)

    }

    /**
     * 楼栋地图数据*/
    def mapBuildingData()
    {
        def db = new groovy.sql.Sql(dataSource)
        def mapDataStatus = ""
        def result = new HashMap()
        def mapDataList = new ArrayList()
        def conditionQueryParam = new HashMap()
        def allDataList = new ArrayList()
        try
        {
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = mapDataList
                render([result: result] as JSON)
                return
            }
            def accountManageAccountSql = " select o.manageAccount.id from AccountManageAccount o where o.account.id = ${userAccount.id} "
            def accountIdList = AccountManageAccount.executeQuery(accountManageAccountSql).toString().replace("[", "(").replace("]", ")")
            def buildSql = " select new map ( e.x as  lng,e.y as lat ) from Estate e where e.isEstate = true "
            def estateSql = "select e.x as  lng,e.y as lat, c.define as define ,c.color as color ,e.id as id ,e.external_Id as externalId ,e.community_Name as communityName ," + "e.source_Address as sourceAddress ,e.source_Build_Area as sourceBuildArea ,e.loans_Balance as loansBalance , e.loans_Duration as loansDuration ," + "e.x as lng, e.y as lat,e.loans_Amount as loansAmount,e.loans_Evaluate_Total_Price as loansEvaluateTotalPrice ," + "to_char(e.start_Date,'yyyy-MM-dd') as startDate ,e.cltv*100 as cltv from Estate e " + "join custom_dimension c on c.parent_id=(select d.id from custom_dimension d where d.code='cltv') and e.cltv > c.min_value and e.cltv <= c.max_value " + "join area a on a.area_id=e.city_id join area b on b.area_id=e.district_id where  e.is_Estate = true and e.account_id in " + accountIdList + " "
            buildSql = mapConditionSql(buildSql, params, conditionQueryParam, user, 1)
            estateSql += mapConditionSQL(params)
            buildSql += " group by e.x ,e.y "
            //查询小区楼栋的x/y
            mapDataList = Estate.executeQuery(buildSql, conditionQueryParam)
            //查询坐标相同押品信息
            for (
                int j = 0;
                    j < mapDataList.size();
                    j++)
            {
                def cltvList = CustomDimension.executeQuery(" from  CustomDimension c where c.parent.code = 'cltv' order by c.orderNumber desc  ")
                def list = new ArrayList()
                def newList = new ArrayList()
                cltvList.each {
                    def str = estateSql
                    def x = mapDataList[j].lng
                    def y = mapDataList[j].lat
                    def customMinValue = it.minValue
                    def customMaxValue = it.maxValue
                    str += " and e.x = ${x} and e.y = ${y} and e.cltv ${it.minOperator} ${customMinValue} and e.cltv ${it.maxOperator} ${customMaxValue} order by e.cltv desc "
                    def elist = db.rows(str).asList()
                    if (elist.size() > 0)
                    {
                        list.add(elist)
                    }
                }
                for (
                    int i = 0;
                        i < list.size();
                        i++)
                {
                    def map = new HashMap<String, Object>()
                    map.put("estateList", list[i])
                    map.put("lng", list[i]["lng"][0])
                    map.put("lat", list[i]["lat"][0])
                    map.put("define", list[i]["define"][0])
                    map.put("color", list[i]["color"][0])
                    newList.add(map)
                }
                allDataList.add(newList)
            }
            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = allDataList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /**
     * 银行楼栋数据2
     * @return
     */
    def mapBuildingBankData2()
    {
        def db = new groovy.sql.Sql(dataSource)
        def mapDataStatus = ""
        def result = new HashMap()
        def mapDataList = new ArrayList()
        def conditionQueryParam = new HashMap()
        def allDataList = new ArrayList()
        def community = new ArrayList()
        def city = params["city"]
        try
        {
            def communityId = Long.parseLong(params["communityId"])
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = mapDataList
                render([result: result] as JSON)
                return
            }
            def communitySql = " select new map (count(e.id) as estateCount ) from estate  e where e.isEstate = true "
            def accountManageAccountSql = " select o.manageAccount.id from AccountManageAccount o where o.account.id = ${userAccount.id} "
            def accountIdList = AccountManageAccount.executeQuery(accountManageAccountSql).toString().replace("[", "(").replace("]", ")")
            def buildSql = " select new map ( e.x as  lng,e.y as lat ) from Estate e where e.isEstate = true "
            def estateSql = "select e.x as  lng,e.y as lat, c.define as define ,c.color as color ,e.id as id ,e.external_Id as externalId ,e.community_Name as communityName ," + "e.source_Address as sourceAddress ,e.source_Build_Area as sourceBuildArea ,e.loans_Balance as loansBalance , e.loans_Duration as loansDuration ," + "e.x as lng, e.y as lat,e.loans_Amount as loansAmount,e.loans_Evaluate_Total_Price as loansEvaluateTotalPrice ," + "to_char(e.start_Date,'yyyy-MM-dd') as startDate ,e.cltv*100 as cltv from Estate e " + "left join custom_dimension c on c.parent_id=(select d.id from custom_dimension d where d.code='cltv') and e.cltv >= c.min_value and e.cltv < c.max_value " + "left join area a on a.area_id=e.city_id left join area b on b.area_id=e.district_id where  e.is_Estate = true and e.account_id in " + accountIdList + " "
            communitySql = mapConditionSql(communitySql, params, conditionQueryParam, user, 1)
            buildSql = mapConditionSql(buildSql, params, conditionQueryParam, user, 1)
            estateSql += mapConditionSQL(params)
            communitySql += " group by e.communityId ,e.communityName "
            buildSql += " group by e.x ,e.y "
            //查询小区楼栋的x/y
            mapDataList = Estate.executeQuery(buildSql, conditionQueryParam)
            //查询坐标相同押品信息
            for (
                int j = 0;
                    j < mapDataList.size();
                    j++)
            {
                def cltvList = CustomDimension.executeQuery(" from  CustomDimension c where c.parent.code = 'cltv' order by c.orderNumber desc  ")
                def list = new ArrayList()
                def newList = new ArrayList()
                cltvList.each {
                    def str = estateSql
                    def x = mapDataList[j].lng
                    def y = mapDataList[j].lat
                    def customMinValue = it.minValue
                    def customMaxValue = it.maxValue
                    str += " and e.x = ${x} and e.y = ${y} and e.cltv >= ${customMinValue} and e.cltv < ${customMaxValue} order by e.cltv desc "
                    def elist = db.rows(str).asList()
                    if (elist.size() > 0)
                    {
                        list.add(elist)
                    }
                }
                for (
                    int i = 0;
                        i < list.size();
                        i++)
                {
                    def map = new HashMap<String, Object>()
                    map.put("estateList", list[i])
                    map.put("lng", list[i]["lng"][0])
                    map.put("lat", list[i]["lat"][0])
                    map.put("define", list[i]["define"][0])
                    map.put("color", list[i]["color"][0])
                    newList.add(map)
                }
                allDataList.add(newList)
            }
            //小区详情
            community = apiService.community(city, communityId)
            mapDataStatus = "true"
            result["community"] = community["data"]
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = allDataList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["community"] = community
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /**
     * 信用贷楼栋数据
     * @return
     */
    def mapBuildingBankData3()
    {
        def db = new groovy.sql.Sql(dataSource)
        def mapDataStatus = ""
        def result = new HashMap()
        def mapDataList = new ArrayList()
        def conditionQueryParam = new HashMap()
        def allDataList = new ArrayList()
        def community = new ArrayList()
        def communityId = Long.parseLong(params["communityId"])
        def city = params["city"]
        try
        {
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = mapDataList
                render([result: result] as JSON)
                return
            }
            def communitySql = " select new map (count(e.id) as estateCount ) from estate  e where e.isEstate = true "
            def accountManageAccountSql = " select o.manageAccount.id from AccountManageAccount o where o.account.id = ${userAccount.id} "
            def accountIdList = AccountManageAccount.executeQuery(accountManageAccountSql).toString().replace("[", "(").replace("]", ")")
            def buildSql = " select new map ( e.x as  lng,e.y as lat ) from Estate e where e.isEstate = true "
            def estateSql = "select e.x as  lng,e.y as lat, m.is_Anti_Fraud as isAntiFraud ,m.borr_Name as borrName ,m.id as mortgageId,e.id as id ,e.external_Id as externalId ,e.community_Name as communityName ," + "e.source_Address as sourceAddress ,e.source_Build_Area as sourceBuildArea ,e.loans_Balance as loansBalance , e.loans_Duration as loansDuration ," + "e.x as lng, e.y as lat,e.loans_Amount as loansAmount,e.loans_Evaluate_Total_Price as loansEvaluateTotalPrice ," + "to_char(e.start_Date,'yyyy-MM-dd') as startDate ,e.cltv*100 as cltv from Estate e " + "right join Mortgage m on m.estate_id = e.id " + "left join area a on a.area_id=e.city_id left join area b on b.area_id=e.district_id where  e.is_Estate = true and e.account_id in " + accountIdList + " "
            communitySql = mapConditionSql(communitySql, params, conditionQueryParam, user, 1)
            buildSql = mapConditionSql(buildSql, params, conditionQueryParam, user, 1)
            estateSql += mapConditionSQL(params)
            communitySql += " group by e.communityId ,e.communityName "
            buildSql += " group by e.x ,e.y "
            //查询小区楼栋的x/y
            mapDataList = Estate.executeQuery(buildSql, conditionQueryParam)
            //查询坐标相同押品信息
            for (
                int j = 0;
                    j < mapDataList.size();
                    j++)
            {
                def newList = new ArrayList()
                def x = mapDataList[j].lng
                def y = mapDataList[j].lat
                //命中
                def str = estateSql
                str += " and e.x = ${x} and e.y = ${y} and m.is_Anti_Fraud = true "
                def elist = db.rows(str).asList()
                if (elist.size() > 0)
                {
                    def map = new HashMap<String, Object>()
                    map.put("estateList", elist)
                    map.put("lng", elist[0]["lng"])
                    map.put("lat", elist[0]["lat"])
                    map.put("isAntiFraud", "true")
                    newList.add(map)
                }
                //未命中
                def str1 = estateSql
                str1 += " and e.x = ${x} and e.y = ${y} and m.is_Anti_Fraud = false "
                def elist1 = db.rows(str1).asList()
                if (elist1.size() > 0)
                {
                    def map = new HashMap<String, Object>()
                    map.put("estateList", elist1)
                    map.put("lng", elist1[0]["lng"])
                    map.put("lat", elist1[0]["lat"])
                    map.put("isAntiFraud", "false")
                    newList.add(map)
                }
                allDataList.add(newList)
            }
            //小区详情
            community = apiService.community(city, communityId)
            mapDataStatus = "true"
            result["community"] = community["data"]
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = allDataList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["community"] = community
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /**
     * 银行楼栋数据
     * @return
     */
    def mapBuildingBankData()
    {
        def mapDataStatus = ""
        def result = new HashMap()
        def mapDataList = new ArrayList()
        def boundaryList = new ArrayList()
        def conditionQueryParam = new HashMap()
        def allDataList = new ArrayList()
        def isEstate = ""
        def city = params["city"]
        def communityId = Long.parseLong(params["communityId"])
        try
        {
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = mapDataList
                render([result: result] as JSON)
                return
            }
            def buildSql = " select new map(e.buildingId as buildingId,e.x as  lng,e.y as lat ) from Estate e where 1=1 "
            def estateSql = "select new map(e.id as id ) " + "from Estate e where e.isEstate= true "
            buildSql = mapConditionSql(buildSql, params, conditionQueryParam, user, 1)
            estateSql = mapConditionSql(estateSql, params, conditionQueryParam, user, 1)
            buildSql += " group by e.x ,e.y , e.buildingId"
            //查询小区楼栋的x/y
            //mapDataList = Estate.executeQuery(buildSql,conditionQueryParam)
            mapDataList = apiService.building(city, communityId, "")["data"]
            mapDataList.each {
                it.put("isEstate", new String())
                def x = it.x as String
                def y = it.y as String
                def bdzb = Trans.MctToBd(x, y)
                bdzb = bdzb.split(',')
                x = bdzb[0]
                y = bdzb[1]
                it.lng = Double.parseDouble(x)
                it.lat = Double.parseDouble(y)
            }
            //查询坐标相同押品信息
            for (
                int j = 0;
                    j < mapDataList.size();
                    j++)
            {
                def str = estateSql
                //                def x = mapDataList[j].lng
                //                def y = mapDataList[j].lat
                //                str += " and e.x = :x and e.y = :y "
                //                conditionQueryParam['x'] = x
                //                conditionQueryParam['y'] = y
                def buildingId = mapDataList[j].buildingId
                str += " and e.buildingId = :buildingId "
                conditionQueryParam['buildingId'] = Long.parseLong(buildingId)
                def elist = Estate.executeQuery(str, conditionQueryParam)
                if (elist.size() > 0)
                {
                    isEstate = "true"
                }
                else
                {
                    isEstate = "false"
                }
                mapDataList[j].put("isEstate", isEstate)
            }
            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
            result["boundaryList"] = boundaryList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
            result["boundaryList"] = boundaryList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /**
     * 小区图片数据
     * @return
     */
    def searchPicture()
    {
        def result = new HashMap()
        def pictureList = new ArrayList()
        def mapDataStatus = ""
        try
        {
            def communityId = Long.parseLong(params['communityId'])
            def city = params['city']
            pictureList = apiService.picture(city, communityId, 5, 3)
            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result['pictureList'] = pictureList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["pictureList"] = pictureList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /**
     * 地图省份或城市或行政区列表
     * @return
     */
    def areaList()
    {
        def city = params["city"]
        def province = params["province"]
        def areaList = new ArrayList()
        if (city && city == "-1")
        {
            //全国下的省
            areaList = Estate.executeQuery(" select distinct e.province as area from  Estate e ")
        }
        else if (province && province != "-1")
        {
            //省下的城市
            areaList = Estate.executeQuery(" select distinct e.city as area from  Estate e where e.city.parentArea.zoneNumber= '${province}' ")
        }
        else if (city && city != "-1")
        {
            //城市下的行政区
            areaList = Estate.executeQuery(" select distinct e.district as area from  Estate e where e.district.parentArea.zoneNumber= '${city}' ")
        }
        render([areaList: areaList] as JSON)
    }

    def customList()
    {
        if (params["custom"] == '')
        {
            return ''
        }
        else
        {
            render([customList: CustomDimension.executeQuery(" from  CustomDimension c where c.parent.code = '${params['custom']}' order by orderNumber ")] as JSON)
        }
    }

    /**
     * 地图条件原生SQL*/
    def mapConditionSQL(params)
    {
        def city = params["city"]
        //城市
        def district = params["district"]
        //行政区
        def precinct = params["precinct"]
        //片区
        def startDateStart = params["startDateStart"]
        //放款时间
        def startDateEnd = params["startDateEnd"]
        //放款时间
        def loansDuration = params["loansDuration"]
        //贷款时间
        def risk = params["risk"]
        //押品风险
        def buildArea = params["buildArea"]
        //面积区域
        def communityId = params['communityId']
        //小区id
        def communityName = params['communityName']
        //小区名
        def buildingType = params['buildingType']
        //资产类型
        def warningType = params["warningType"]
        //预警类型
        def loansBalanceStart = params["loansBalanceStart"]
        def loansBalanceEnd = params["loansBalanceEnd"]
        def name = params['name']
        def customCode = params['customCode']
        def customValue = params['customValue']
        def leftTop = params["leftTop"]
        def rightUp = params["rightUp"]
        String[] left = new String[30]
        String[] right = new String[30]
        //地图可视范围
        def estateSql = " "
        if (city && city != 'null' && city != '-1')
        {
            estateSql += "and a.zone_Number = '${city}' "
        }
        if (district && district != 'null' && district != '-1')
        {
            estateSql += "and b.zone_Number = '${district}' "
        }
        if (precinct && precinct != "-1")
        {
            estateSql += "and p.id = ${precinct} "
        }
        if (buildingType && buildingType != '')
        {
            estateSql += " and e.building_Type_id = ${buildingType} "
        }
        if (loansBalanceStart && loansBalanceStart != '')
        {
            estateSql += "and e.loans_Balance >= ${loansBalanceStart * 10000} "
        }
        if (loansBalanceEnd && loansBalanceEnd != '')
        {
            estateSql += "and e.loans_Balance <= ${loansBalanceEnd * 10000}"
        }
        if (startDateStart)
        {
            //中文日期转换
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            estateSql += "and to_char(e.start_Date,'YYYY-MON-DD HH24:MI:SS') >=  '${dates1}' "
        }
        if (startDateEnd)
        {
            //中文日期转换
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            estateSql += " and to_char(e.start_Date,'YYYY-MON-DD HH24:MI:SS') <= '${datee1}' "
        }
        if (loansDuration && loansDuration != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    def loansDurationMin = it.minValue
                    def loansDurationMax = it.maxValue
                    estateSql += " and e.loans_Duration > ${loansDurationMin} and e.loans_Duration<= ${loansDurationMax} "
                }
            }
        }
        if (risk && risk != '')
        {
            def riskList = RiskDimension.list()
            riskList.each {
                if (it?.label == risk)
                {
                    def minValue = it.minValue / 100
                    def maxValue = it.maxValue / 100
                    estateSql += " and e.cltv >= ${minValue} and e.cltv< ${maxValue} "
                }
            }
        }
        if (buildArea && buildArea != '')
        {
            def buildAreaList = BuildAreaDimension.list()
            buildAreaList.each {
                if (it?.label == buildArea)
                {
                    def buildAreaMin = new BigDecimal(it.minValue)
                    def buildAreaMax = new BigDecimal(it.maxValue)
                    estateSql += " and e.build_Area > ${buildAreaMin} and e.build_Area <= ${buildAreaMax} "
                }
            }
        }
        if (communityId && communityId != "")
        {
            estateSql += " and e.community_Id = ${communityId} "
        }
        if (communityName && communityName != "")
        {
            estateSql += " and e.community_Name like '%${communityName}%' "
        }
        if (name && name != "")
        {
            estateSql += " and e.name like '%${name}%' "
        }
        if (customValue && customCode)
        {
            def customValueList = CustomDimension.findAllByParent(CustomDimension.findAllByCode(customCode))
            ImprovedNamingStrategy dns = new ImprovedNamingStrategy()
            String column = dns.propertyToColumnName(customCode)
            customValueList.each {
                if (it?.label == customValue)
                {
                    def customMinValue = it.minValue
                    def customMaxValue = it.maxValue
                    estateSql += " and e.${column} >= ${customMinValue} and e.${column} < ${customMaxValue} "
                }
            }
        }
        if (leftTop && rightUp)
        {
            left = leftTop.split(",")
            right = rightUp.split(",")
            def maxX = right[0]
            def maxY = right[1]
            def minX = left[0]
            def minY = left[1]
            estateSql += " and e.x <= '${maxX}' and e.x >= '${minX}' and e.y <= '${maxY}' and e.y >= '${minY}' "
        }
        if (warningType)
        {
            if (warningType == '1' || warningType == '14')
            {
                def estateListSql = " select r.collateral.estate.id from RiskWarning r where r.type.code = '${warningType}' and r.isWarning = '1' "
                def estateList = RiskWarning.executeQuery(estateListSql)
                def sql = "( null )"
                if (estateList)
                {
                    def str = estateList.toString()
                    sql = "(" + str.substring(1, str.lastIndexOf("]")) + ")"
                }
                estateSql += " and e.id in " + sql
            }

        }
        return estateSql
    }

    /**
     * 地图条件*/
    def mapConditionSql(estateSql, params, conditionQueryParam, user, type)
    {
        def city = params["city"]
        //城市
        def district = params["district"]
        //行政区
        def precinct = params["precinct"]
        //片区
        def startDateStart = params["startDateStart"]
        //放款时间
        def startDateEnd = params["startDateEnd"]
        //放款时间
        def loansDuration = params["loansDuration"]
        //贷款时间
        def risk = params["risk"]
        //押品风险
        def buildArea = params["buildArea"]
        //面积区域
        def communityId = params['communityId']
        //小区id
        def communityName = params['communityName']
        //小区名
        def buildingType = params['buildingType']
        //资产类型
        def warningType = params['warningType']
        //预警类型
        def loansBalanceStart = params["loansBalanceStart"]
        def loansBalanceEnd = params["loansBalanceEnd"]
        def name = params['name']
        def customCode = params['customCode']
        def customValue = params['customValue']
        def leftTop = params["leftTop"]
        def rightUp = params["rightUp"]
        String[] left = new String[30]
        String[] right = new String[30]
        //地图可视范围
        estateSql = Estate.FilterAccount(estateSql, conditionQueryParam, user)

        if (city && city != 'null' && city != '-1')
        {
            if (type == 1)
            {
                estateSql += " and e.city.zoneNumber = :city "
            }
            else if (type == 2)
            {
                estateSql += " and b.parentArea.zoneNumber = :city"
            }

            conditionQueryParam['city'] = city
        }
        if (district && district != 'null' && district != '-1')
        {
            estateSql += "and e.district.zoneNumber = :district "
            conditionQueryParam['district'] = district
        }
        if (precinct && precinct != "-1")
        {
            estateSql += "and a.precinct.id = :precinct "
            conditionQueryParam['precinct'] = precinct
        }
        if (buildingType && buildingType != '')
        {
            estateSql += " and e.buildingType.id = :buildingType "
            conditionQueryParam['buildingType'] = Long.parseLong(buildingType)
        }
        if (loansBalanceStart && loansBalanceStart != '')
        {
            estateSql += "and e.loansBalance >= :loansBalanceStart "
            conditionQueryParam['loansBalanceStart'] = new BigDecimal(loansBalanceStart) * 10000
        }
        if (loansBalanceEnd && loansBalanceEnd != '')
        {
            estateSql += "and e.loansBalance <= :loansBalanceEnd "
            conditionQueryParam['loansBalanceEnd'] = new BigDecimal(loansBalanceEnd) * 10000
        }
        if (startDateStart)
        {
            //中文日期转换
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            estateSql += "and to_char(e.startDate,'YYYY-MON-DD HH24:MI:SS') >=  :dates1 "
            conditionQueryParam['dates1'] = dates1
        }
        if (startDateEnd)
        {
            //中文日期转换
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            estateSql += " and to_char(e.startDate,'YYYY-MON-DD HH24:MI:SS') <= :datee1 "
            conditionQueryParam['datee1'] = datee1
        }
        if (loansDuration && loansDuration != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    def loansDurationMin = it.minValue
                    def loansDurationMax = it.maxValue
                    estateSql += " and e.loansDuration > :loansDurationMin and e.loansDuration<= :loansDurationMax "
                    conditionQueryParam['loansDurationMin'] = loansDurationMin
                    conditionQueryParam['loansDurationMax'] = loansDurationMax
                }
            }
        }
        if (risk && risk != '')
        {
            def riskList = RiskDimension.list()
            riskList.each {
                if (it?.label == risk)
                {
                    def minValue = it.minValue / 100
                    def maxValue = it.maxValue / 100
                    estateSql += " and e.cltv >= :minValue and e.cltv< :maxValue "
                    conditionQueryParam['minValue'] = minValue
                    conditionQueryParam['maxValue'] = maxValue
                }
            }
        }
        if (buildArea && buildArea != '')
        {
            def buildAreaList = BuildAreaDimension.list()
            buildAreaList.each {
                if (it?.label == buildArea)
                {
                    def buildAreaMin = new BigDecimal(it.minValue)
                    def buildAreaMax = new BigDecimal(it.maxValue)
                    estateSql += " and e.buildArea > :buildAreaMin and e.buildArea <= :buildAreaMax "
                    conditionQueryParam['buildAreaMin'] = buildAreaMin
                    conditionQueryParam['buildAreaMax'] = buildAreaMax
                }
            }
        }
        if (communityId && communityId != "")
        {
            estateSql += " and e.communityId = :communityId "
            conditionQueryParam['communityId'] = Long.parseLong(communityId)
        }
        if (communityName && communityName != "")
        {
            estateSql += " and e.communityName like :communityName "
            conditionQueryParam['communityName'] = "%" + communityName + "%"
        }
        if (name && name != "")
        {
            estateSql += " and e.name like :name "
            conditionQueryParam['name'] = "%" + name + "%"
        }
        if (customValue && customCode)
        {
            def customValueList = CustomDimension.findAllByParent(CustomDimension.findAllByCode(customCode))
            customValueList.each {
                if (it?.label == customValue)
                {
                    def customMinValue = it.minValue
                    def customMaxValue = it.maxValue
                    estateSql += " and e.${customCode} >= ${customMinValue} and e.${customCode} < ${customMaxValue} "
                }
            }
        }
        if (leftTop && rightUp)
        {
            left = leftTop.split(",")
            right = rightUp.split(",")
            def maxX = right[0]
            def maxY = right[1]
            def minX = left[0]
            def minY = left[1]
            estateSql += " and e.x <= :maxX and e.x >= :minX and e.y <= :maxY and e.y >= :minY "
            conditionQueryParam['maxX'] = Double.parseDouble(maxX)
            conditionQueryParam['minX'] = Double.parseDouble(minX)
            conditionQueryParam['maxY'] = Double.parseDouble(maxY)
            conditionQueryParam['minY'] = Double.parseDouble(minY)
        }
        if (warningType)
        {
            if (warningType == '1' || warningType == '14')
            {
                estateSql += " and e.id in ( select r.collateral.estate.id from RiskWarning r where r.type.code = :warningType and r.isWarning = '1' )"
                conditionQueryParam["warningType"] = warningType
            }
            else
            {
                estateSql += " and e.communityId in ( select r.community.communityId from RiskWarning r where r.type.code = :warningType and r.isWarning = '1' )"
                conditionQueryParam["warningType"] = warningType
            }

        }
        return estateSql
    }

    /**
     * 热力图数据*/
    def heatData()
    {
        def heatSql = " select new map (e.communityId as communityId , e.communityName as communityName ,avg(e.x) as lng ,avg(e.y) as lat ,count(e.id) as count ) from Estate e where 1 = 1 "
        def sql = getSql(params)
        heatSql += sql
        heatSql += " group by e.communityId ,e.communityName "
        def result = Estate.executeQuery(heatSql).asList()
        render([result: result] as JSON)

    }

    def point()
    {
        respond(view: "point")
    }

    def cluster()
    {
        respond(view: "cluster")
    }

    def cluster1()
    {
        def districtName = params['district']
        def city = Area.findByZoneNumber(params['city'])
        def district = Area.findByNameAndParentArea(districtName, city)?.zoneNumber
        redirect controller: "newMap", action: "cluster", params: [currentMap: 'clusterMap', flag: 'true', city: params['city'], district: district]
        return
    }

    /*************************聚合图数据*********************************/
    /**押品城市*/
    def estateCityDate()
    {
        def sql = " select distinct e.city as city from  Estate e "
        def result = Estate.executeQuery(sql).asList()
        render([result: result] as JSON)
    }

    /**全国聚合图数据*/
    def clusterDate()
    {
        def db = new groovy.sql.Sql(dataSource)
        def city = params['city']
        //def city = 110100
        def sql = " select  a.longitude as lng ,e.city_id,a.zone_number,a.name as name,sum(e.loans_evaluate_total_price)  as loansEvaluateTotalPrice," + "a.zone_number, a.latitude as lat ,count(e.id) as count  FROM " + " estate e  inner join area a  on   "
        if (city && city != '-1')
        {
            sql += " a.area_id=e.district_id where 1 = 1 and a.parent_area = (select b.area_id from area b where b.zone_number='${city}')"
        }
        else
        {
            sql += " a.area_id=e.city_id  "
        }
        sql += " group by a.longitude ,a.name,a.latitude,e.city_id,a.zone_number  "
        def result = db.rows(sql).asList()
        render([result: result] as JSON)
    }

    /**小区押品详细信息*/
    def searchEstateDate()
    {

        def city = params['zoneNumber']
        // def city = "330100"
        def district = params['zoneNumber']
        //def district = "110113"
        def communityId = params['communityId']
        //def communityId = "4968521"
        //查询小区楼栋的x/y
        def sql = "select new map(e.x as  lng,e.y as lat ) from Estate e where 1=1 "
        if (city && city != '-1')
        {
            sql += "  and e.city.zoneNumber = '${city}'"
        }
        if (district && district != '-1')
        {
            sql += " and e.district.zoneNumber = '${district}'"
        }
        if (communityId)
        {
            sql += " and e.communityId=${communityId} "
        }
        sql += "group by e.x,e.y "

        def eList = Estate.executeQuery(sql).asList()
        eList.each {
            it.put("estateList", new ArrayList<>())
        }
        //查询坐标相同押品信息
        for (
            int j = 0;
                j < eList.size();
                j++)
        {
            def x = eList[j].lng
            def y = eList[j].lat
            def result = Estate.executeQuery("select new map(e.id as id,e.externalId as externalId, e.sourceAddress as sourceAddress ,e.sourceBuildArea as sourceBuildArea ,e.communityName" + " as communityName,e.loansBalance as loansBalance,e.cltv*100 as cltv ,e.loansEvaluateTotalPrice as loansEvaluateTotalPrice ,e.createdDate as createdDate) " + "from Estate e where e.x= ${x} and e.y=${y}")
            def list = (List) eList[j].get("estateList")
            //            result.each {
            //                def cltv = ((it.loansBalance / it.loansEvaluateTotalPrice) * 100).setScale(2, BigDecimal.ROUND_HALF_UP)
            //                it.cltv = cltv
            //            }
            list.add(result)
            eList[j].put("estateList", list)
        }
        /* def eList=new ArrayList<Map<String,Object>>()
        def eList = Estate.executeQuery("select new map(e.x as  lng,e.y as lat ) from Estate e where communityId=${communityId} group by e.x,e.y" ).asList()
         eList.each {
             it.put("estateList",new ArrayList<>())
         }
         for (int i=0;i<result.size();i++){
             if(eList.size()==0){
                 continue
             }else{
                 for (int j=0;j<eList.size();j++){
                     if(eList[j].lng==result[i].get("lng")&&eList[j].lat==result[i].get("lat")){
                         def list =(List)eList[j].get("estateList")
                         list.add(result[j])
                         eList[j].put("estateList",list)
                     }
                 }
             }

         }*/

        render([result: eList] as JSON)
    }

    /**搜索页面接口*/
    def searchCommunityDate()
    {
        def db = new groovy.sql.Sql(dataSource)
        def city = params['city']
        def district = params['zoneNumber']
        //押品状态
        def status = params['status']
        //放贷日期
        def startDate = params["startDate"]
        def endDate = params["endDate"]
        //押品风险
        def risk = params['risk']
        //面积区域
        def buildArea = params["buildArea"]
        //抵押年限
        def loansDuration = params["loansDuration"]
        //小区名称
        def communityName = params['communityName']
        def sql = " select avg(e.x) as lng ,e.community_id as communityId, a.zone_number as zoneNumber,e.city_id as cityId ,e.community_name as" + " communityName,sum(e.loans_evaluate_total_price)  as loansEvaluateTotalPrice," + " avg(e.y) as lat ,count(e.id) as count  from " + " Estate e  inner join Area a  on a.area_id=e.district_id where 1 = 1   "
        if (city && city != '-1')
        {
            sql += "  and e.city_id = '${city}'"
        }
        if (district && district != '-1')
        {
            sql += " and a.zone_number = '${district}'"
        }
        if (status)
        {
            sql += " and e.status = '${status}'"
        }
        if (startDate)
        {
            def dates = startDate.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += "and e.start_date >= '${dates1}'  "
        }
        if (endDate)
        {
            def datee = endDate.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += "and e.start_date <= '${datee1}' "
        }
        if (risk)
        {
            def riskList = RiskDimension.list()
            riskList.each {
                if (it?.label == risk)
                {
                    def minValue = it.minValue / 100
                    def maxValue = it.maxValue / 100
                    sql += " and e.cltv > ${minValue} and e.cltv <= ${maxValue} "
                }
            }
        }
        if (buildArea && buildArea != '')
        {
            def buildAreaStr = ''
            if (buildArea == '0-90')
            {
                buildAreaStr = '> 0 <= 90 '
            }
            else if (buildArea == '90-144')
            {
                buildAreaStr = '> 90.001 <= 144 '
            }
            else if (buildArea == '144以上')
            {
                buildAreaStr = '>144 '
            }
            sql += " and e.source_build_area  " + buildAreaStr
        }
        if (loansDuration)
        {
            def loansDurationStr = ''
            if (loansDuration == '1-10')
            {
                loansDurationStr = '> 1 <= 10 '
            }
            else if (loansDuration == '10-20')
            {
                loansDurationStr = '> 10 <= 20 '
            }
            else if (loansDuration == '20年以上')
            {
                loansDurationStr = '>20 '
            }
            sql += " and e.loans_duration  " + loansDurationStr
        }
        if (communityName)
        {
            sql += " and e.community_name like '%${communityName}%'"
        }
        sql += "group by e.community_name,e.community_id,e.city_id,a.zone_number  "
        def result = db.rows(sql).asList()
        render([result: result] as JSON)
    }

    /**
     * 地图搜索-小区的数据
     * @return
     */
    def searchCommunity()
    {
        def communitySql = "select new map ( e.communityId as communityId ,e.communityName as communityName ,avg(e.x) as lng , avg(e.y) as lat ,count(e.id) as count ,sum(e.loansAmount) as loansAmount) from Estate e where 1 = 1"
        def sql = getSql(params)
        communitySql += sql
        communitySql += "group by e.communityId,e.communityName "
        def result = Estate.executeQuery(communitySql).asList()
        render([result: result] as JSON)
    }

    /**
     * 地图搜索-小区押品的数据
     * @return
     */
    def searchCommunityEstateDate()
    {
        def buildSql = " select new map(e.x as  lng,e.y as lat ) from Estate e where 1 =1 "
        def estateSql = "select new map(e.id as id ,e.externalId as externalId ,e.communityName as communityName ,e.sourceAddress as sourceAddress ,e.sourceBuildArea as sourceBuildArea ,e.loansBalance as loansBalance , e.loansDuration as loansDuration ,e.x as lng, e.y as lat,e.loansAmount as loansAmount,e.loansEvaluateTotalPrice as loansEvaluateTotalPrice ,to_char(e.startDate,'yyyy-MM-dd') as startDate ,e.cltv*100 as cltv ) " + "from Estate e where 1=1 "
        def sql = getSql(params)
        buildSql += sql
        estateSql += sql
        buildSql += " group by e.x,e.y"
        //查询小区楼栋的x/y
        def result = Estate.executeQuery(buildSql).asList()
        result.each {
            it.put("estateList", new ArrayList<>())
        }
        //查询坐标相同押品信息
        for (
            int j = 0;
                j < result.size();
                j++)
        {
            def str = estateSql
            def x = result[j].lng
            def y = result[j].lat
            str += " and e.x= ${x} and e.y=${y} "
            def elist = Estate.executeQuery(str)
            def list = (List) result[j].get("estateList")
            list.add(elist)
            result[j].put("estateList", list)
        }
        render([result: result] as JSON)

    }

    /**地图-条件Sql*/
    def getSql(params)
    {
        def city = params['city']
        def district = params['district']
        //押品风险
        def risk = params['risk']
        //放贷日期
        def startDate = params["startDateStarts"]
        def endDate = params["startDateEnds"]
        //面积区域
        def buildArea = params["buildArea"]
        //抵押年限
        def loansDuration = params["loansDuration"]
        //小区名称
        def communityName = params['communityName']
        //小区id
        def communityId = params['communityId']
        def leftTop = params['leftTop']
        def rightUp = params['rightUp']
        String[] left = new String[30]
        String[] right = new String[30]
        def sql = " "
        if (city && city != '-1')
        {
            sql += "  and e.city.zoneNumber = '${city}'"
        }
        if (district && district != '-1')
        {
            sql += " and e.district.zoneNumber = '${district}'"
        }
        if (risk)
        {
            def riskList = RiskDimension.list()
            riskList.each {
                if (it?.label == risk)
                {
                    def minValue = it.minValue / 100
                    def maxValue = it.maxValue / 100
                    sql += " and e.cltv > '${minValue}' and e.cltv <= '${maxValue}' "
                }
            }
        }
        if (startDate)
        {
            def dates = startDate.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(e.startDate ,'YYYY-MM-DD HH24:MI:SS') >= '${dates1}' "
        }
        if (endDate)
        {

            def datee = endDate.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += "  and to_char(e.startDate,'YYYY-MM-DD HH24:MI:SS') <= '${datee1}' "
        }
        if (buildArea)
        {
            def buildAreaStr = ''
            if (buildArea == '0-90')
            {
                buildAreaStr = 'and e.sourceBuildArea > 0 and e.sourceBuildArea <= 90 '
            }
            else if (buildArea == '90-144')
            {
                buildAreaStr = 'and e.sourceBuildArea > 90 and e.sourceBuildArea <= 144 '
            }
            else if (buildArea == '144以上')
            {
                buildAreaStr = 'and e.sourceBuildArea > 144 '
            }
            sql += buildAreaStr
        }
        if (loansDuration)
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    sql += " and e.loansDuration > '${it.minValue}' and e.loansDuration <= '${it.maxValue}' "
                }
            }
        }
        if (communityName)
        {
            sql += " and e.communityName like '%${communityName}%'"
        }
        if (communityId)
        {
            sql += " and e.communityId= '${communityId}'"
        }
        if (leftTop && rightUp)
        {
            left = leftTop.split(",")
            right = rightUp.split(",")
            def maxX = right[0]
            def maxY = right[1]
            def minX = left[0]
            def minY = left[1]
            sql += " and e.x <= '${maxX}' and e.x >= '${minX}' and e.y <= '${maxY}' and e.y >= '${minY}'"
        }
        return sql
    }

    /**
     * 地图搜索-押品的数据
     * @return
     */
    def estateDate()
    {
        def x = params['lng']
        def y = params['lat']
        def sql = "select new map(e.id as id ,e.sourceAddress as sourceAddress  ) " + "from Estate e  where 1=1"
        if (x && y)
        {
            sql += " and e.x = '${x}' and e.y = '${y}'"
        }
        def result = Estate.executeQuery(sql).asList()
        render([result: result] as JSON)

    }

    /**散点图押品信息*/
    def searchPointDate()
    {
        //def city = "330100"
        def city = params['zoneNumber']
        def leftTop = params['leftTop']
        def rightUp = params['rightUp']
        String[] left = new String[30]
        String[] right = new String[30]

        //查询小区楼栋的x/y
        def sql = "select new map(e.x as  lng,e.y as lat,count(e.id) as count) from Estate e where 1=1 "
        if (city && city != '-1')
        {
            sql += "  and e.city.zoneNumber = '${city}'"
        }
        if (leftTop && rightUp)
        {
            left = leftTop.split(",")
            right = rightUp.split(",")
            def maxX = right[0]
            def maxY = right[1]
            def minX = left[0]
            def minY = left[1]
            sql += " and e.x <= '${maxX}' and e.x >= '${minX}' and e.y <= '${maxY}' and e.y >= '${minY}'"
        }
        sql += "group by e.x,e.y "
        def eList = Estate.executeQuery(sql).asList()
        /*  eList.each {
              def cltv = ((it.loansBalance / it.loansEvaluateTotalPrice) * 100).setScale(2, BigDecimal.ROUND_HALF_UP)
              it.cltv = cltv
          }*/
        render([result: eList] as JSON)
    }
}

